package com.example.demo.mapper;

import com.example.demo.entity.Team;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Optional;

@Mapper
public interface TeamMapper {

    // 根据课程ID查找团队
    @Select("SELECT * FROM teams WHERE course_id = #{courseId}")
    List<Team> findByCourseId(@Param("courseId") Long courseId);

    // 查找学生所在的团队
    @Select("SELECT t.* FROM teams t JOIN team_members tm ON t.id = tm.team_id WHERE tm.student_id = #{studentId}")
    List<Team> findTeamsByStudentId(@Param("studentId") Long studentId);

    // 检查团队名称是否在课程中已存在
    @Select("SELECT COUNT(*) > 0 FROM teams WHERE team_name = #{teamName} AND course_id = #{courseId}")
    boolean existsByTeamNameAndCourseId(@Param("teamName") String teamName, @Param("courseId") Long courseId);

    // 根据团队名称和课程ID查找团队
    @Select("SELECT * FROM teams WHERE team_name = #{teamName} AND course_id = #{courseId}")
    Optional<Team> findByTeamNameAndCourseId(@Param("teamName") String teamName, @Param("courseId") Long courseId);

    // 查找课程中的所有团队（按创建时间排序）
    @Select("SELECT * FROM teams WHERE course_id = #{courseId} ORDER BY created_at DESC")
    List<Team> findByCourseIdOrderByCreatedAtDesc(@Param("courseId") Long courseId);

    // 统计课程中的团队数量
    @Select("SELECT COUNT(*) FROM teams WHERE course_id = #{courseId}")
    long countByCourseId(@Param("courseId") Long courseId);

    // 根据团队ID列表查找团队
    @Select({
            "<script>",
            "SELECT * FROM teams WHERE id IN",
            "<foreach collection='teamIds' item='teamId' open='(' separator=',' close=')'>",
            "#{teamId}",
            "</foreach>",
            "</script>"
    })
    List<Team> findByIdIn(@Param("teamIds") List<Long> teamIds);

    // 根据ID查找团队
    @Select("SELECT * FROM teams WHERE id = #{id}")
    Optional<Team> findById(@Param("id") Long id);

    // 插入团队
    @Insert("INSERT INTO teams (team_name, course_id, created_at) VALUES (#{teamName}, #{courseId}, #{createdAt})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Team team);

    // 更新团队
    @Update("UPDATE teams SET team_name = #{teamName} WHERE id = #{id}")
    int update(Team team);

    // 删除团队
    @Delete("DELETE FROM teams WHERE id = #{id}")
    int deleteById(@Param("id") Long id);

    // 计算团队排名（简化实现）
    @Select("SELECT COUNT(*) + 1 FROM teams t1 WHERE t1.course_id = " +
            "(SELECT course_id FROM teams WHERE id = #{teamId}) AND " +
            "(SELECT COALESCE(SUM(sp.score), 0) FROM team_members tm " +
            "JOIN student_course_progress sp ON tm.student_id = sp.student_id " +
            "WHERE tm.team_id = t1.id AND sp.score IS NOT NULL) > " +
            "(SELECT COALESCE(SUM(sp.score), 0) FROM team_members tm " +
            "JOIN student_course_progress sp ON tm.student_id = sp.student_id " +
            "WHERE tm.team_id = #{teamId} AND sp.score IS NOT NULL)")
    Integer calculateTeamRanking(@Param("teamId") Long teamId);
}